/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO.InformationFilter;

import Info.UserCategoryClickInfo;
import DAO.DAOAccess;
import Info.PeriodInfo;
import Info.UserInfo;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.AbstractList;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author tran tuan tai
 */
public class UsrCategoryClickDAO
{

    public void DoEmpty()
    {
         try
        {
            java.sql.Connection conn = DAOAccess.GetConnection();
            String sql = "DELETE FROM USERCATEGORYCLICK";
            PreparedStatement stat = conn.prepareCall(sql);
            stat.execute();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }

    public List<UserCategoryClickInfo> GetByUserPeriod(PeriodInfo period, UserInfo user)
    {
        List<UserCategoryClickInfo> result = new ArrayList<UserCategoryClickInfo>();
        String query = "SELECT * FROM USERCATEGORYCLICK WHERE USER = " + user.id + " PERIOD = " + period.GetID();
        ResultSet r = DAOAccess.ExecuteQuery(query);
        try
        {
            int idxID = r.findColumn("ID");
            int idxPeriod = r.findColumn("Period");
            int idxCategory = r.findColumn("category");
            int idxUser = r.findColumn("USER");
            int idxClick = r.findColumn("CLICK");
            int idxPercent = r.findColumn("percentclick");

            r.beforeFirst();
            while(r.next())
            {
                   UserCategoryClickInfo u = new UserCategoryClickInfo();
                   u.id = r.getInt(idxID);
                   u.user = r.getInt(idxUser);
                   u.period = r.getInt(idxPeriod);
                   u.category = r.getInt(idxCategory);
                   u.click = r.getInt(idxClick);
                   u.percent = r.getDouble(idxPercent);

                   result.add(u);
            }

            r.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }

        
        return result;
    }

    public Double GetProb(UserInfo user, Integer idCategory, PeriodInfo period)
    {
        String query = "SELECT PercentCLICK FROM USERCATEGORYCLICK WHERE USER = " + user.id + " AND PERIOD = " + period.GetID()
                + " AND CATEGORY = " + idCategory;
        ResultSet r = DAOAccess.ExecuteQuery(query);
        try
        {
            r.beforeFirst();
            while(r.next())
            {
                return r.getDouble(1);
            }

        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return null;
    }

    public int Insert(UserCategoryClickInfo uccInfo)
    {
        int id = -1;
        java.sql.Connection conn = DAOAccess.GetConnection();
        String sql = "INSERT INTO USERCATEGORYCLICK(USER, PERIOD, CATEGORY, CLICK, PERCENTCLICK) VALUES(?, ?, ?, ?, ?)";
        try
        {
            PreparedStatement stat = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            stat.setInt(1, uccInfo.user);
            stat.setInt(2, uccInfo.period);
            stat.setInt(3, uccInfo.category);
            stat.setInt(4, uccInfo.click);
            stat.setDouble(5, uccInfo.percent);

            stat.executeUpdate();
            ResultSet result = stat.getGeneratedKeys();
            result.first();
            id = result.getInt(1);

            stat.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return id;
    }
}
